<?php

class AgendaCurso {

    function post_addAgendaCurso($agenda) {

        try {

            if ($agenda->id) {

                //update

                $sqlUpdate = "UPDATE agendaCursos SET descricao=:descricao,dataInicial=:dataInicial,dataFinal=:dataFinal,
                    horaInicial=:horaInicial,horaFinal=:horaFinal,status=:status,observacoes=:observacoes,idMinistrante=:idMinistrante,idCurso=:idCurso
                    WHERE id = :id";

                $stmt = DB::prepare($sqlUpdate);
                $stmt->bindParam("id", $agenda->id);
                $stmt->bindParam("descricao", $agenda->descricao);
                $stmt->bindParam("dataInicial", $agenda->dataInicial);
                $stmt->bindParam("dataFinal", $agenda->dataFinal);
                $stmt->bindParam("horaInicial", $agenda->horaInicial);
                $stmt->bindParam("horaFinal", $agenda->horaFinal);
                $stmt->bindParam("status", $agenda->status);
                $stmt->bindParam("observacoes", $agenda->obsAgenda);
                $stmt->bindParam("idMinistrante", $agenda->idMinistrante);
                $stmt->bindParam("idCurso", $agenda->idCurso);

                $stmt->execute();
            }
            else {
                //insert

                $sqlInsert = "INSERT INTO agendaCursos (descricao,dataInicial,dataFinal,horaInicial,horaFinal,status,observacoes,idMinistrante,idCurso)
                VALUES (:descricao,:dataInicial,:dataFinal,:horaInicial,:horaFinal,:status,:observacoes,:idMinistrante,:idCurso)";

                $stmt = DB::prepare($sqlInsert);
                $stmt->bindParam("descricao", $agenda->descricao);
                $stmt->bindParam("dataInicial", $agenda->dataInicial);
                $stmt->bindParam("dataFinal", $agenda->dataFinal);
                $stmt->bindParam("horaInicial", $agenda->horaInicial);
                $stmt->bindParam("horaFinal", $agenda->horaFinal);
                $stmt->bindParam("status", $agenda->status);
                $stmt->bindParam("observacoes", $agenda->obsAgenda);
                $stmt->bindParam("idMinistrante", $agenda->idMinistrante);
                $stmt->bindParam("idCurso", $agenda->idCurso);

                $stmt->execute();
            }
        }
        catch (Exception $exc) {
            throw new Exception("Informe a Mescker " + $exc);
        }
    }

    function post_delete($agendamento) {
        try {
            $sql  = "DELETE FROM agendaCursos WHERE id = :id";
            $stmt = DB::prepare($sql);
            $stmt->bindParam("id", $agendamento->idAgendamento);
            $stmt->execute();
        }
        catch (Exception $e) {
            throw new Exception($e->getMessage());
        }
    }

    function get_listAll() {

        $sql = "SELECT agenda.id,agenda.descricao,DATE_FORMAT(agenda.dataInicial, '%d/%m/%Y') as dataInicial,
            DATE_FORMAT(agenda.dataFinal, '%d/%m/%Y') as dataFinal,DATE_FORMAT(agenda.horaInicial,'%H:%i') as horaInicial,
            DATE_FORMAT(agenda.horaFinal, '%H:%i') as horaFinal,c.nome as nomeCurso,u.nome as nomeMinistrante,agenda.status
            FROM agendaCursos agenda INNER JOIN cursos c ON c.id = agenda.idCurso INNER JOIN ministrantes m ON m.id = agenda.idMinistrante
            INNER JOIN usuarios u ON u.id = m.idUsuario ORDER BY agenda.id ASC";


        $stmt   = DB::prepare($sql);
        $stmt->execute();
        $result = $stmt->fetchAll();
        return $result;
    }

    function get_listStatus($filtroStatus) {

        $sql = "SELECT agenda.id,agenda.descricao,DATE_FORMAT(agenda.dataInicial, '%d/%m/%Y') as dataInicial,
            DATE_FORMAT(agenda.dataFinal, '%d/%m/%Y') as dataFinal,DATE_FORMAT(agenda.horaInicial,'%H:%i') as horaInicial,
            DATE_FORMAT(agenda.horaFinal, '%H:%i') as horaFinal,c.nome as nomeCurso,u.nome as nomeMinistrante,agenda.status
            FROM agendaCursos agenda INNER JOIN cursos c ON c.id = agenda.idCurso INNER JOIN ministrantes m ON m.id = agenda.idMinistrante
            INNER JOIN usuarios u ON u.id = m.idUsuario  WHERE agenda.status LIKE '$filtroStatus' ORDER BY agenda.id ASC";


        $stmt   = DB::prepare($sql);
        $stmt->execute();
        $result = $stmt->fetchAll();
        return $result;
    }

    function get_listUpd($id) {

        $sql    = "SELECT id,descricao,dataInicial,dataFinal,horaInicial,horaFinal,idCurso,idMinistrante,status,observacoes
            FROM agendaCursos WHERE id = :id";
        $stmt   = DB::prepare($sql);
        $stmt->bindParam("id", $id);
        $stmt->execute();
        $result = $stmt->fetch();
        return $result;
    }

    function post_verificaCursoEncerrado() {

        try {
            date_default_timezone_set('America/Sao_Paulo');
            $sql  = "UPDATE agendaCursos SET status = 'Encerrado' WHERE DATEDIFF(:dataAtual,DATE_FORMAT(dataFinal,'%d/%m/%y')) >= 0 AND 
                TIMEDIFF(:horaAtual,DATE_FORMAT(horaFinal,'%H:%i')) > 0 AND status = 'Confirmado' ";
            $stmt = DB::prepare($sql);
            $stmt->bindParam("dataAtual", date("d/m/y"));
            $stmt->bindParam("horaAtual", date("H:i"));
            $stmt->execute();
        }
        catch (Exception $exc) {
            throw new Exception($exc->getMessage());
        }
    }

}
?>
